WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
/*
	Description:	Upload employee's address information
	Name:			Crystal
	Date:			2006/2/2
*/
	l_new_person_id						crst_fnd_per_all_people_map.person_id_n%type;
	l_new_address_id						crst_fnd_per_addresses.address_id%type;
	l_new_obj_ver_number					crst_fnd_per_addresses.object_version_number%type;
	l_effective_date						per_all_people_f.start_date%type;
	l_country							crst_fnd_per_addresses.country%type;
	eff_date_error						EXCEPTION;
	person_id_error						EXCEPTION;

	CURSOR cur_fnd_addr IS
		SELECT addr.*
		FROM crst_fnd_per_addresses addr
		--WHERE person_id= 340
		ORDER BY person_id;
BEGIN
   	FOR rec IN cur_fnd_addr LOOP
      	BEGIN
      		SELECT 	person_id_n
      		INTO   	l_new_person_id
      		FROM	crst_fnd_per_all_people_map
      		WHERE 	person_id = rec.person_id;
      	EXCEPTION
      		WHEN OTHERS THEN
         		RAISE person_id_error;
      	END;
      	BEGIN
         	SELECT MIN(papf.effective_start_date)
         	INTO   l_effective_date
         	FROM   per_all_people_f papf
         	WHERE  papf.person_id =l_new_person_id;
         	
      	EXCEPTION
      		WHEN OTHERS THEN
         		RAISE eff_date_error;
      	END;

      	IF rec.country = 'HK_GLB' THEN
			l_country := 'HK';
		ELSE
			l_country := rec.country;
      	END IF;
		--dbms_output.put_line(to_char(l_new_person_id));
      	hr_person_address_api.create_person_address
  				(
  				p_effective_date                =>	l_effective_date
  				,p_person_id                     =>	l_new_person_id
  				,p_primary_flag                  =>	rec.primary_flag
  				,p_style                         => rec.style
  				--,p_date_from                     =>	rec.date_from
  				,p_date_from                     => l_effective_date
  				,p_date_to                       => rec.date_to
  				,p_address_type                  => rec.address_type
  				--,p_address_line1                 => rec.address_line1
  				,p_address_line1                 => 'Yuen Long'
  				,p_address_line2                 => rec.address_line2
  				,p_address_line3                 => rec.address_line3
  				,p_town_or_city                  => rec.town_or_city
  				,p_region_1                      => rec.region_1
  				,p_region_2                      => rec.region_2
  				,p_region_3                      => rec.region_3
  				,p_postal_code                   => rec.postal_code
  				--,p_country                       => rec.country
  				,p_country                       => l_country
  				,p_telephone_number_1            => rec.telephone_number_1
  				,p_telephone_number_2            => rec.telephone_number_2
  				,p_telephone_number_3            => rec.telephone_number_3
  				,p_addr_attribute_category       => rec.addr_attribute_category
  				,p_addr_attribute1               => rec.addr_attribute1
  				,p_addr_attribute2               =>	rec.addr_attribute2
  				,p_addr_attribute3               => rec.addr_attribute3
  				,p_addr_attribute4               => rec.addr_attribute4
  				,p_addr_attribute5               => rec.addr_attribute5
  				,p_addr_attribute6               => rec.addr_attribute6
  				,p_addr_attribute7               => rec.addr_attribute7
  				,p_addr_attribute8               => rec.addr_attribute8
  				,p_addr_attribute9               => rec.addr_attribute9
  				,p_addr_attribute10              => rec.addr_attribute10
  				,p_addr_attribute11              => rec.addr_attribute11
  				,p_addr_attribute12              => rec.addr_attribute12
  				,p_addr_attribute13              => rec.addr_attribute13
  				,p_addr_attribute14              => rec.addr_attribute14
  				,p_addr_attribute15              => rec.addr_attribute15
  				,p_addr_attribute16              => rec.addr_attribute16
  				,p_addr_attribute17              => rec.addr_attribute17
  				,p_addr_attribute18              => rec.addr_attribute18
  				,p_addr_attribute19              => rec.addr_attribute19
  				,p_addr_attribute20              => rec.addr_attribute20
  				,p_add_information13             => rec.add_information13
  				,p_add_information14             => rec.add_information14
  				,p_add_information15             => rec.add_information15
  				,p_add_information16             => rec.add_information16
  				,p_add_information17             => rec.add_information17
  				,p_add_information18             => rec.add_information18
  				,p_add_information19             => rec.add_information19
  				,p_add_information20             => rec.add_information20
  			--	,p_party_id                      => rec.party_id
  				,p_address_id                    => l_new_address_id
  				,p_object_version_number         => l_new_obj_ver_number
  		 );

  		 /*hr_hk_person_address_api.create_hk_person_address
  				(
  				 p_effective_date       	=>	l_effective_date
  				,p_person_id                =>  l_new_person_id
  				,p_primary_flag             =>  rec.primary_flag
  				,p_date_from                =>  rec.date_from
  				,p_address_line1            =>  rec.address_line1
  				,p_country                  =>  rec.country
  				,p_address_id               =>  l_new_address_id
  				,p_object_version_number    =>  l_new_obj_ver_number
  		 );  		 */

		 /*INSERT INTO crst_fnd_per_addresses_map (address_id, address_id_n)
   									 	VALUES  (rec.address_id, l_new_address_id);*/
		UPDATE per_addresses
		SET	address_line1 = rec.address_line1,
			party_id = rec.party_id,
			country = rec.country
			--date_from = rec.date_from 
		WHERE address_id = l_new_address_id;

   	END LOOP;

   	COMMIT;
    dbms_output.put_line('COMMIT complete.');
EXCEPTION
	WHEN eff_date_error THEN
		dbms_output.put_line('Error : effective date error' );
		ROLLBACK;
	WHEN person_id_error THEN
		dbms_output.put_line(' Error: cannot find new person id ');
		ROLLBACK;
	WHEN OTHERS THEN
		dbms_output.put_line(to_char(l_new_person_id));
		dbms_output.put_line (SQLERRM);
		ROLLBACK;
END;
/

